﻿--04072012 HoangTQ 
--		update table StudyCourse for field StartDate to not null
--		update table StudyCourse for field IsComplete to not null and default False value
--		update table StudyCourseDetail for field IsPass to not null and default False value
--

USE master
GO
--DROP DATABASE CreditEducationDb
GO
CREATE DATABASE CreditEducationDb
GO
USE CreditEducationDb
GO
CREATE TABLE Course
(
	Id int primary key identity(1,1),
	[Name] nvarchar(4) unique not null
)
GO
CREATE TABLE Faculty
(
	Id int primary key identity(1,1),
	[Name] nvarchar(50) unique not null
)
GO
CREATE TABLE Major
(
	Id int primary key identity(1,1),
	[Name] nvarchar(50) unique not null,
	IdFaculty int references Faculty(Id) not null
)
GO
CREATE TABLE Capacity
(
	Id int primary key identity(1,1),
	MaxCredit int not null,
	MinCredit int not null,
	IdMajor int references Major(Id) not null,
	IdCourse int references Course(Id) not null
)
-------------------------------------------------
-------------------------------------------------
GO
CREATE TABLE KindOfSubject
(
	Id int primary key identity(1,1),
	[Name] nvarchar(50) unique not null
)
GO
CREATE TABLE [Subject]
(
	Id int primary key identity(1,1),
	[Name] nvarchar(50) unique not null,
	NumberOfCredit int not null,
	IdKindOfSubject int references KindOfSubject(Id) not null
)
GO
CREATE TABLE SubjectRequire
(
	Id int primary key identity(1,1),
	IdSubject int references [Subject](Id) not null,
	IdSubjectRequire int references [Subject](Id) not null
)
-------------------------------------------------
-------------------------------------------------
GO
CREATE TABLE [User]
(
	Id int primary key identity(1,1),
	[Name] nvarchar(50) not null,
	Birthday datetime ,
	Picture varbinary(MAX),
	MoreInformation nvarchar(250),
	IdFaculty int references Faculty(Id) not null,
	IdMajor int references Major(Id) not null,
)
-------------------------------------------------
-------------------------------------------------
GO
CREATE TABLE StudyPlan
(
	Id int primary key identity(1,1),
	[Name] nvarchar(30) unique not null,
	[Detail] nvarchar(100),
	StartDate datetime not null,
	IdMajor int references Major(Id) not null,
)
GO
CREATE TABLE StudyCourse
(
	Id int primary key identity(1,1),
	IsComplete bit not null default 'False', -- 0 = No, 1 = Yes
	StartDate datetime not null,
	IdCourse int references Course(Id) not null,
	IdStudyPlan int references StudyPlan(Id) ON DELETE CASCADE not null,
)
GO
CREATE TABLE StudyCourseDetail
(
	Id int primary key identity(1,1),
	IsPass bit not null default 'False', -- 0 = N, 1 = Y 	
	IdStudyCourse int references StudyCourse(Id) ON DELETE CASCADE not null,
	IdSubject int references [Subject](Id) not null,
)
GO
-------------------------------------------------
-------------------------------------------------
CREATE TABLE Syllabus
(
	Id int primary key identity(1,1),
	IdMajor int references Major(Id) not null,
	IdSubject int references [Subject](Id) not null,
	IdCourse int references Course(Id) not null
)
GO
-------------------------------------------------
---------------- INSERT DATA --------------------
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[Course] ON
GO
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(1,'I')
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(2,'II')
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(3,'III')
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(4,'IV')
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(5,'V')
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(6,'VI')
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(7,'VII')
INSERT INTO [dbo].[Course]([Id],[Name])VALUES(8,'VIII')
GO
SET IDENTITY_INSERT [dbo].[Course] OFF
GO
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[Faculty] ON
GO
INSERT INTO [dbo].[Faculty]([Id],[Name])VALUES(1,N'Công nghệ thông tin')
INSERT INTO [dbo].[Faculty]([Id],[Name])VALUES(2,N'Quản lý')
INSERT INTO [dbo].[Faculty]([Id],[Name])VALUES(3,N'Giáo dục học')
GO
SET IDENTITY_INSERT [dbo].[Faculty] OFF
GO
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[Major] ON
GO
INSERT INTO [dbo].[Major]([Id],[Name],[IdFaculty])VALUES(1,N'Tin học ứng dụng',1)
INSERT INTO [dbo].[Major]([Id],[Name],[IdFaculty])VALUES(2,N'Quản lý giáo dục',2)
INSERT INTO [dbo].[Major]([Id],[Name],[IdFaculty])VALUES(3,N'Tâm lý giáo dục',3)
GO
SET IDENTITY_INSERT [dbo].[Major] OFF
GO
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[Capacity] ON
GO
INSERT INTO [dbo].[Capacity]([Id],[MaxCredit],[MinCredit],[IdMajor],[IdCourse])VALUES(1,40,20,1,1)
--more
GO
SET IDENTITY_INSERT [dbo].[Capacity] OFF
GO
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[KindOfSubject] ON
GO
INSERT INTO [dbo].[KindOfSubject]([Id],[Name])VALUES(1,N'Bắt buộc')
INSERT INTO [dbo].[KindOfSubject]([Id],[Name])VALUES(2,N'Tự chọn')
GO
SET IDENTITY_INSERT [dbo].[KindOfSubject] OFF
GO
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[Subject] ON
GO
INSERT INTO [dbo].[Subject]([Id],[Name],[NumberOfCredit],[IdKindOfSubject])VALUES(1,N'Toán cao cấp 1',4,1)
INSERT INTO [dbo].[Subject]([Id],[Name],[NumberOfCredit],[IdKindOfSubject])VALUES(2,N'Toán cao cấp 2',4,1)
INSERT INTO [dbo].[Subject]([Id],[Name],[NumberOfCredit],[IdKindOfSubject])VALUES(3,N'Giải tích 1',3,1)
INSERT INTO [dbo].[Subject]([Id],[Name],[NumberOfCredit],[IdKindOfSubject])VALUES(4,N'Giải tích 2',3,1)
INSERT INTO [dbo].[Subject]([Id],[Name],[NumberOfCredit],[IdKindOfSubject])VALUES(5,N'Giải tích 3',4,1)
--more
GO
SET IDENTITY_INSERT [dbo].[Subject] OFF
GO
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[SubjectRequire] ON
GO
INSERT INTO [dbo].[SubjectRequire]([Id],[IdSubject],[IdSubjectRequire])VALUES(1,2,1)
INSERT INTO [dbo].[SubjectRequire]([Id],[IdSubject],[IdSubjectRequire])VALUES(2,4,3)
INSERT INTO [dbo].[SubjectRequire]([Id],[IdSubject],[IdSubjectRequire])VALUES(3,5,4)
INSERT INTO [dbo].[SubjectRequire]([Id],[IdSubject],[IdSubjectRequire])VALUES(4,5,3)
GO
SET IDENTITY_INSERT [dbo].[SubjectRequire] OFF
GO
-------------------------------------------------
SET IDENTITY_INSERT [dbo].[User] ON
GO
INSERT INTO [dbo].[User]([Id],[Name],[Birthday],[MoreInformation],[IdFaculty],[IdMajor])VALUES(1,N'Lê Thị Thanh Hải','Aug 1 1990','I am a student',1,1)
GO
SET IDENTITY_INSERT [dbo].[SubjectRequire] OFF
GO
-------------------------------------------------
